package com.zr.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zr.dao.StudentDao;
import com.zr.model.Student;
import com.zr.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class StudentDaoImpl implements StudentDao{
	
	
//获取当前学生信息
	@Override
	public JSONArray getStudentBySid(int sid) {
		JSONArray js=new JSONArray();
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT student.sid ,student.bid ,student.saddress ,student.sage ,student.sname, student.spnumber,student.ssex, student.stime ");
		sql.append(" FROM student");
		sql.append(" Where sid=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			ResultSet  rs = pst.executeQuery();
			while (rs.next()) {
				JSONObject j=new JSONObject();
				j.put("id",rs.getInt("sid"));
				j.put("bid",rs.getInt("bid"));
				j.put("name",rs.getString("sname"));
				j.put("age",rs.getString("sage"));
				j.put("sex",rs.getString("ssex"));
				j.put("address",rs.getString("saddress"));
				j.put("time",rs.getString("stime"));
				j.put("pnumber",rs.getString("spnumber"));
				js.add(j);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return js;
	
		
	}
	
	
//修改当前个人信息
	@Override
	public  boolean  updateStudent(int sid,String sname,String sage,String ssex,String saddress,String stime,String spnumber){
		boolean flag=false;
		StringBuffer sql=new StringBuffer("");
		sql.append(" Update student set sname=?,sage=?,ssex=?,saddress=?,stime=?,spnumber=?");
		sql.append(" Where sid=?");
		Connection con=JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setString(1, sname);
			pst.setString(2, sage);
			pst.setString(3, ssex);
			pst.setString(4, saddress);
			pst.setString(5, stime);
			pst.setString(6, spnumber);
			pst.setInt(7, sid);
			int i=pst.executeUpdate();
			if (i>0) {
				flag=true;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
    //筛选学生已选课程
	public int sidAndCid(int sid,int cid){
		int flag1=0;
		StringBuffer sql=new StringBuffer("");
		sql.append("select s_c.sid from s_c");
		sql.append(" where s_c.sid=? and s_c.cid=?");
		Connection con=JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2,cid);
			ResultSet rs=pst.executeQuery();
			if (rs.next()) {
				 flag1=rs.getInt("s_c.sid");
					
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return flag1;
	}
	
   //添加学生未选课程
	@Override
	public boolean addCourseByCid(int sid,int cid) {
		StudentDaoImpl sdl=new StudentDaoImpl();
		int tid=sdl.getTeacherBytid(cid);
		boolean flag1=false;
		int flag=sdl.sidAndCid(sid, cid);
		if(flag==0){
		boolean flag2=sdl.addCourseBytid(sid, tid);
		StringBuffer sql=new StringBuffer("");
		sql.append("INSERT INTO s_c(sid,cid)");
		sql.append(" VALUES(?,?)");	
		Connection con=JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2,cid);
			int i=pst.executeUpdate();
			if (i>0) {
				flag1=true;
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		}
		return flag1;
		
		
		
}
	
	
//学生添加课程后添加对应课程下的老师
	public boolean addCourseBytid(int sid,int tid) {
		StudentDaoImpl sdl=new StudentDaoImpl();
		boolean flag1=false;
		int a=sdl.sidAndtid(sid, tid);
		if(a==0){
		StringBuffer sql=new StringBuffer("");
		sql.append("INSERT INTO s_t(sid,tid)");
		sql.append(" VALUES(?,?)");
		Connection con=JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2,tid);
			int i=pst.executeUpdate();
			if (i>0) {
				flag1=true;
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		
		return flag1;
	}
	//根据课程id查找老师id
	public int getTeacherBytid(int cid) {
		int tid=0;
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT tid ");
		sql.append(" FROM course");
		sql.append(" Where cid=?");
		Connection  con = JDBCUtil.getConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(sql.toString());
			pst.setInt(1, cid);
			ResultSet  rs = pst.executeQuery();
			if (rs.next()) {
				tid=rs.getInt("tid");
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tid;
	
		
	}
	//查找老师对应的学生
	public int sidAndtid(int sid,int tid){
		int flag1=0;
		StringBuffer sql=new StringBuffer("");
		sql.append("select s_t.sid from s_t");
		sql.append(" where s_t.sid=? and s_t.tid=?");
		Connection con=JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2,tid);
			ResultSet rs=pst.executeQuery();
			if (rs.next()) {
				 flag1=rs.getInt("s_t.sid");
					
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return flag1;
	}
	

   
	
}






